package com.zr.sms.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.sms.dao.GinventoryInfoDtoDao;
import com.zr.sms.dto.GinventoryInfoDto;
import com.zr.sms.util.JDBCUtil;

public class GinventoryInfoDtoDaoImpl implements GinventoryInfoDtoDao{

	@Override
	public int getCountByGinventory() {
		Connection con=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		int count=0;
		con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("select count(gid) count from goods");
		try {
			stmt=con.prepareStatement(sql.toString());
		    rs=stmt.executeQuery();
		    if(rs.next()){
		    	count=rs.getInt("count");
		    }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return count;
	}

	@Override
	public List<GinventoryInfoDto> selectGinventoryInfosByGinventory(int start,int pagesize ,int ginventory) {
		 Connection con=null;
		 PreparedStatement stmt=null;
		 ResultSet rs=null;
		 List<GinventoryInfoDto> ginventoryinfos=new ArrayList<GinventoryInfoDto>();
		 con=JDBCUtil.getConnection();
		 StringBuffer sql=new StringBuffer("SELECT goods.gid,goods.gname,goods.ginventory,goods.gcost,supplier.sname");
		 sql.append(" FROM goods ,s_g,supplier");
		 sql.append(" where goods.gid=s_g.gid and s_g.sid=supplier.sid and goods.ginventory<? limit ?,?");
	
         try {
			stmt=con.prepareStatement(sql.toString());
			stmt.setInt(1, ginventory);
			stmt.setInt(2, start);
			stmt.setInt(3, pagesize);
			rs=stmt.executeQuery();
			while(rs.next()){
				GinventoryInfoDto g=new GinventoryInfoDto();
				g.setGid(rs.getInt("goods.gid"));
				g.setGname(rs.getString("goods.gname"));
				g.setGinventory(rs.getInt("goods.ginventory"));
			    g.setGcost(rs.getDouble("goods.gcost"));
				g.setSname(rs.getString("supplier.sname"));
				ginventoryinfos.add(g);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
   
		return ginventoryinfos;
	}

}
